#include "sql.h"

//初始化数据库
void SQL::initDatabase()
{
    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setHostName("");
        database.setPort(56);
        database.setDatabaseName("QChat.db");
        database.setUserName("root");
        database.setPassword("123456");
    }

    createRegInfoTable();
    createAccountIPTable();
    createGroupChatTable();
    createGroupChatInfoTable();
    createHisRecordsTable();
    createFriendApplicationTable();
    qsrand(QTime(0,0,0).secsTo(QTime::currentTime()));

    database.open();
    QSqlQuery *sqlquert = new QSqlQuery(database);
    sqlquert->exec("SELECT * FROM RegInfo_table");
    while(sqlquert->next())
    {
        qDebug() << sqlquert->value(0).toString() << sqlquert->value(1).toString() << sqlquert->value(2).toString() << sqlquert->value(3).toString();
    }
}

//生成一个不重复的十位数的AccountID
QString SQL::registerAccount(QString password, QString phone_num, QString nick_name)
{
    if(phone_num.size() != 11)
    {
        qDebug() << "手机号码长度错误，注册失败";
        return QString();
    }
    for(int i = 0; i < 11; i ++)
    {
        if(phone_num.at(i) < '0' || phone_num.at(i) > '9')
        {
            qDebug() << "手机号码类型错误，注册失败";
            return QString();
        }
    }
    if(password.size() < 6)
    {
        qDebug() << "密码过短，注册失败";
        return QString();
    }

    QString account;
    if(database.open())
    {
        QSqlQuery *sqlquery = new QSqlQuery(database);
        while(true)
        {
            account.clear();
            for(int i = 0; i < 10; i ++)
            {
                int tem = qrand() % 9;
                account += QString::number(tem);
            }
            if(account.mid(0,1) == '0')
                account = account.mid(1);
            sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
            sqlquery->addBindValue(QVariant(account));
            sqlquery->exec();
            sqlquery->next();
            if(sqlquery->value(0).toInt() == 0)
                break;
        }
        qDebug() << "注册成功，您的账号是:" << account;
        insertRegInfoTable(account, password, phone_num, nick_name);
        delete sqlquery;
        database.close();
    }
    return account;
}

//返回注册账号的手机号码
QString SQL::getPhoneNumber(QString account)
{
    QString phone_num;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT PhoneNumber FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        phone_num = sqlquery->value(0).toString();
        qDebug() << "该账号的手机号码为：" << phone_num;
        delete sqlquery;
        database.close();
    }
    return phone_num;
}

//注销一个账号
void SQL::removeAccount(QString account)
{
    if(database.open())
    {
        deleteAccountIPTableOnAccount(account);

        QVector<QString> group_list;
        getGroupIdOnAccount(account, group_list);
        for(auto &l : group_list)
        {
            kickOneFromGroup(l, account);
        }

        deleteFriendApplicationOnAccount(account);
        deleteRegInfoTable(account);
        qDebug() << "账号ID" << account << "已经被注销或不存在！";

        database.close();
    }
}

//解散一个群组
void SQL::removeGroup(QString group_id)
{
    if(database.open())
    {
        deleteGroupChatInfoTableOnGroupId(group_id);
        deleteHisRecordsTableOnGroupId(group_id);
        deleteGroupChatTable(group_id);
        database.close();
    }
}

//为一个账号增加登陆IP
void SQL::addAccountIP(QString account, QString ip)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            insertAccountIPTable(account, ip);
            qDebug() << "已为账号" << account << "增加登陆IP或此账号ip信息已记录" << ip;
        }
        else qDebug() << "该账号不存在";
        delete sqlquery;
        database.close();
    }
}

//为一个账号删去一个ip
void SQL::removeAccountIP(QString account, QString ip)
{
    if(database.open())
    {
        deleteAccountIPTable(account, ip);
        database.close();
    }
}

//检查账号ip是否已经存在
bool SQL::checkAccountIP(QString account, QString ip)
{
    bool flag = false;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM AccountIP_table WHERE AccountID = ? AND IP = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->addBindValue(QVariant(ip));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0) > 0)
        {
            flag = true;
            qDebug() << "此账号已经在此ip有过登陆纪录";
        }
        else qDebug() << "此账号还未在此ip登陆过";
        delete sqlquery;
        database.close();
    }
    return flag;
}

//校验登录信息
bool SQL::verifyLogin(QString account, QString password)
{
    bool flag = false;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ? AND Password = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->addBindValue(QVariant(password));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            flag = true;
            qDebug() << "账号" << account << "认证成功";
        }
        else qDebug() << "账号或密码不对";
        delete sqlquery;
        database.close();
    }
    return flag;
}

//完整的登陆验证
QString SQL::LoginIn(QString account, QString password, QString ip, bool mode)
{
    if(verifyLogin(account, password))
    {
        if(mode == true)
        {
            if(!checkAccountIP(account, ip))
            {
                return QString("ipError");
            }
        }
        addAccountIP(account, ip);
        return QString("LoginSucess");
    }
    else
        return QString("LoginFail");
}

//创建一个新群聊，并返回群号
QString SQL::newGroup(QString account, int is_group, QString group_name)
{
    QString group_id;
    if(database.open())
    {
        QSqlQuery *sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            while(true)
            {
                group_id.clear();
                for(int i = 0; i < 10; i ++)
                {
                    int tem = qrand() % 9;
                    group_id += QString::number(tem);
                }
                if(group_id.mid(0,1) == '0')
                    group_id = group_id.mid(1);
                sqlquery->prepare("SELECT COUNT(*) FROM GroupChat_table WHERE GroupNumber = ?;");
                sqlquery->addBindValue(QVariant(group_id));
                sqlquery->exec();
                sqlquery->next();
                if(sqlquery->value(0).toInt() == 0)
                    break;
            }
            qDebug() << "创建新群聊成功，您的群聊ID是:" << group_id;
            QString s = (is_group == 1)?"群聊":"聊天";
            qDebug() << "该群聊的性质是" << s;
            insertGroupChatTable(group_id, is_group, account, group_name);
            insertGroupChatInfoTable(group_id, account);
        }
        else qDebug() << "该账号不存在，创建新群聊失败";
        delete sqlquery;
        database.close();
    }
    return group_id;
}

//返回群聊是否是真正的群聊
bool SQL::getIsGroup(QString group_id)
{
    bool flag = false;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT IsGroup FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        flag = sqlquery->value(0).toInt();
        delete sqlquery;
        database.close();
    }
    return flag;
}

//向已有的群聊里增加一个账号
void SQL::addOneToGroup(QString group_id, QString account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
            sqlquery->addBindValue(QVariant(account));
            sqlquery->exec();
            sqlquery->next();
            if(sqlquery->value(0).toInt() > 0)
            {
                insertGroupChatInfoTable(group_id, account);
                qDebug() << "已向群聊" << group_id << "添加账号" << account << "或此账号已在该群聊";
            }
            else qDebug() << "该账号不存在，无法向群聊中添加此账号";
        }
        else qDebug() << "该群聊不存在，无法添加账号";
        delete sqlquery;
        database.close();
    }
}

//从已有的群聊中删除一个账号
void SQL::kickOneFromGroup(QString group_id, QString account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM GroupChatInfo_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            if(getGroupLeaderID(group_id) == account)
            {
                removeGroup(group_id);
                qDebug() << "群主退出群聊，已自动解散该群" << group_id;
            }
            else
            {
                deleteGroupChatInfoTable(group_id, account);
                qDebug() << "已经从群聊" << group_id << "中删除账号" << account << "或该账号不存在于此群聊";
                if(sqlquery->value(0).toInt() == 1)
                {
                    removeGroup(group_id);
                    qDebug() << "该群聊所剩人数为0，已自动解散该群聊" << group_id;
                }
            }
        }
        else qDebug() << "不存在此群聊";
        delete sqlquery;
        database.close();
    }
}

//向已有的一个群聊中添加一条历史记录
void SQL::saveHistory(QString group_id, QString news, QString time)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            insertHisRecordsTable(group_id, time, news);
            qDebug() << "已向群聊" << group_id << "中添加时间为" << time << "的消息";
        }
        else qDebug() << "不存在此群聊";
        delete sqlquery;
        database.close();
    }
}

//返回一个群聊中的历史记录，条数为-1时默认返回所有的历史记录
void SQL::getHistory(QString group_id, QVector<QString> &history_list, int num)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            sqlquery->prepare("SELECT News From HisRecords_table WHERE GroupNumber = ? ORDER BY Time DESC");
            sqlquery->addBindValue(QVariant(group_id));
            sqlquery->exec();
            if(num == -1)
            {
                while (sqlquery->next())
                {
                    history_list.push_back(sqlquery->value(0).toString());
                }
            }
            else
            {
                for (int i = 0; i < num && sqlquery->next(); i ++)
                {
                    history_list.push_back(sqlquery->value(0).toString());
                }
            }
            qDebug() << "所获得的信息为：";
            for(auto &s : history_list)
            {
                qDebug() << s;
            }
        }
        else qDebug() << "不存在此群聊";
        delete sqlquery;
        database.close();
    }
}

//返回一个账号的所有群聊
void SQL::getGroupIdOnAccount(QString account, QVector<QString> &group_id_list)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            sqlquery->prepare("SELECT GroupNumber FROM GroupChatInfo_table WHERE AccountID = ?;");
            sqlquery->addBindValue(QVariant(account));
            sqlquery->exec();
            while (sqlquery->next())
            {
                group_id_list.push_back(sqlquery->value(0).toString());
            }
            qDebug() << "所获取到的群聊id为：";
            for(auto &s : group_id_list)
            {
                qDebug() << s;
            }
        }
        else qDebug() << "不存在此账号";
        delete sqlquery;
        database.close();
    }
}

//返回一个群聊中的所有账号
void SQL::getAccountFromGroup(QString group_id, QVector<QString> &account_list)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            sqlquery->prepare("SELECT AccountID FROM GroupChatInfo_table WHERE GroupNumber = ?;");
            sqlquery->addBindValue(QVariant(group_id));
            sqlquery->exec();
            while (sqlquery->next())
            {
                account_list.push_back(sqlquery->value(0).toString());
            }
            qDebug() << "所获取到的账号id为：";
            for(auto &s : account_list)
            {
                qDebug() << s;
            }
        }
        else qDebug() << "不存在此群聊";
        delete sqlquery;
        database.close();
    }
}

//添加一个好友申请
void SQL::addFriendApplication(QString account, QString Info)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            insertFriendApplication(account, Info);
            qDebug() << "已为账号" << account << "纪录一个未完成的好友申请";
        }
        else qDebug() << "该账号不存在";
        delete sqlquery;
        database.close();
    }
}

//移除一个好友申请
void SQL::removeFriendApplication(QString account, QString Info)
{
    if(database.open())
    {
        deleteFriendApplication(account, Info);
        database.close();
    }
}

//获取当前账号所有未处理的好友申请
void SQL::getFriendApplication(QString account, QVector<QString> &friend_application_list)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT COUNT(*) FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        if(sqlquery->value(0).toInt() > 0)
        {
            sqlquery->prepare("SELECT ApplicationInfo FROM FriendApplication_table WHERE AccountID = ?;");
            sqlquery->addBindValue(QVariant(account));
            sqlquery->exec();
            while (sqlquery->next())
            {
                friend_application_list.push_back(sqlquery->value(0).toString());
            }
            qDebug() << "所获取到的好友申请为：";
            for(auto &s : friend_application_list)
            {
                qDebug() << s;
            }
        }
        else qDebug() << "不存在此账号";
        delete sqlquery;
        database.close();
    }
}

QString SQL::getGroupLeaderID(QString group_id)
{
    QString group_leader_id;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT GroupLeaderID FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        group_leader_id = sqlquery->value(0).toString();
        qDebug() << "该群聊的群主是：" << group_leader_id;
        delete sqlquery;
        database.close();
    }
    return group_leader_id;
}

QString SQL::getNickNameFromAccount(QString account)
{
    QString nick_name;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT NickName FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        sqlquery->next();
        nick_name = sqlquery->value(0).toString();
        qDebug() << "该账号的昵称为：" << nick_name;
        delete sqlquery;
        database.close();
    }
    return nick_name;
}

QString SQL::getGroupNameFromGroupid(QString group_id)
{
    QString group_name;
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("SELECT GroupName FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        sqlquery->next();
        group_name = sqlquery->value(0).toString();
        qDebug() << "该群聊的昵称是：" << group_name;
        delete sqlquery;
        database.close();
    }
    return group_name;
}

void SQL::updateNickName(QString account, QString new_name)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("UPDATE RegInfo_table SET NickName = ? WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(new_name));
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        delete sqlquery;
        database.close();
    }
}

void SQL::updatePassword(QString account, QString new_pwd)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("UPDATE RegInfo_table SET Password = ? WHERE AccountID = ?;");
        sqlquery->addBindValue(QVariant(new_pwd));
        sqlquery->addBindValue(QVariant(account));
        sqlquery->exec();
        delete sqlquery;
        database.close();
    }
}

void SQL::updateGroupName(QString group_id, QString new_name)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("UPDATE GroupChat_table SET GroupName = ? WHERE GroupNumber = ?;");
        sqlquery->addBindValue(QVariant(new_name));
        sqlquery->addBindValue(QVariant(group_id));
        sqlquery->exec();
        delete sqlquery;
        database.close();
    }
}

void SQL::addFriend(QString account_1, QString account_2)
{
    if(database.open())
    {
        bool flag = true;
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QVector<QString> group_list_acc1;
        group_list_acc1.clear();
        getGroupIdOnAccount(account_1, group_list_acc1);
        for(auto &l : group_list_acc1)
        {
            if(!getIsGroup(l))
            {
                if(database.open())
                {
                    sqlquery->prepare("SELECT COUNT(*) FROM GroupChatInfo_table WHERE GroupNumber = ? AND AccountID = ?");
                    sqlquery->addBindValue(QVariant(l));
                    sqlquery->addBindValue(QVariant(account_2));
                    sqlquery->exec();
                    sqlquery->next();
                    if(sqlquery->value(0).toInt() >= 0)
                    {
                        flag = false;
                        qDebug() << account_1 << "和" << account_2 << "已经是好友了";
                        break;
                    }
                }
            }
        }
        if(flag)
        {
            addOneToGroup(newGroup(account_2, false), account_1);
        }
        delete sqlquery;
        database.close();
    }
}
